package sgc.beans;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.component.html.HtmlDataTable;
import javax.faces.context.FacesContext;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import sgc.struc.User;

@ManagedBean(name = "userBean")
@RequestScoped
public class UserBean implements Serializable {

	private static final long serialVersionUID = 1L;
	private DataSource ds;
	private HtmlDataTable htmlDataTable;
	private User user;
	private int tabindex;

	public UserBean() {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/sgcweb");
		} catch (NamingException e) {
			e.printStackTrace();
		}
		user = new User();
	}

	public HtmlDataTable getHtmlDataTable() {
		return htmlDataTable;
	}

	public User getUser() {
		return user;
	}

	public void setHtmlDataTable(HtmlDataTable htmlDataTable) {
		this.htmlDataTable = htmlDataTable;
	}

	public void setUser(User user) {
		this.user = user;
	}

	public int getTabindex() {
		return tabindex;
	}

	public void setTabindex(int tabindex) {
		this.tabindex = tabindex;
	}

	public List<User> getUserList() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_GetAllUser() }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.execute();
		ResultSet result = cs.getResultSet();

		List<User> list = new ArrayList<User>();
		while (result.next()) {
			User aux_user = new User();
			aux_user.setName(result.getString("name"));
			aux_user.setPassword(result.getString("password"));
			aux_user.setDescrip(result.getString("descrip"));
			aux_user.setCompany(result.getString("company_code") + " - " + result.getString("company_name"));
			aux_user.setRol(result.getString("rol"));
			aux_user.setSuperuser(result.getBoolean("superuser"));
			aux_user.setStatus(result.getString("status"));
			list.add(aux_user);
		}
		con.close();
		return list;
	}

	public List<String> getNameList() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_GetAllUser() }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.execute();
		ResultSet result = cs.getResultSet();

		List<String> list = new ArrayList<String>();
		String name;
		while (result.next()) {
			name = result.getString(1);
			list.add(name);
		}
		con.close();
		return list;
	}

	public String edit() {
		user = (User) htmlDataTable.getRowData();
		tabindex = 0;
		return "OK";
	}

	public boolean check(String p_name) throws SQLException {
		int aux_id;
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_GetUser(?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setString(1, p_name);
		cs.execute();
		ResultSet result = cs.getResultSet();
		result.next();
		aux_id = result.getInt(1);
		con.close();

		if (aux_id != 0) {
			return true;
		} else {
			return false;
		}
	}

	private int parseInt(String campo) {
		String delims = " - ";
		String[] tokens = campo.split(delims);

		return Integer.parseInt(tokens[0]);
	}
	
	public void save() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");
		
		String simpleProc;
		if (check(user.getName()) == true) {
			simpleProc = "{ CALL sp_updUser(?,?,?,?,?,?,?) }";
		} else {
			simpleProc = "{ CALL sp_addUser(?,?,?,?,?,?,?) }";
		}
		
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setString(1, user.getName());
		cs.setString(2, user.getPassword());
		cs.setString(3, user.getDescrip());
		cs.setInt(4, parseInt(user.getCompany()));
		cs.setString(5, user.getRol());
		cs.setBoolean(6, user.isSuperuser());
		cs.setString(7, user.getStatus());

		cs.execute();
		ResultSet result = cs.getResultSet();
		result.next();
		String aux_message = result.getString(2);
		FacesContext.getCurrentInstance()
				.addMessage(
						null,
						new FacesMessage(FacesMessage.SEVERITY_INFO,
								aux_message, null));
		user = null;

	}
}